package model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.PreparedStatement;

public class DBconnection {

	Connection connection = null;
	Statement statement = null;
	ResultSet resultSet = null;
	
	private static DBconnection instance = null;
	
	private static String sqlUrl = "jdbc:mysql://localhost:3306/carbon";
	private final static String sqlDriver = "com.mysql.jdbc.Driver";
	private static String sqlUser = "root";
    private static String sqlPasswd = "";
    private static Connection conn = null;
    
    private PreparedStatement insertUser = null;
    private PreparedStatement editUser = null;
    private PreparedStatement deleteUser = null;
    private PreparedStatement deleteTransaction = null;
    
    private PreparedStatement insertCategory = null;
    private PreparedStatement deleteCategory = null;
    
    private PreparedStatement insertAccount = null;
    private PreparedStatement deleteAccount = null;
    private PreparedStatement editAccount = null;
    private PreparedStatement selectAccounts = null;
    private PreparedStatement selectTransaction = null;
    private PreparedStatement selectCategories = null;
    private PreparedStatement deleteCategories = null;
    
    
    private PreparedStatement insertTransaction =(PreparedStatement)  null;
    

	private ModelClass modelClass;
	
	public DBconnection(ModelClass modelClass) 
	{
		this.modelClass = modelClass;
	}

public void syncUsersData()
{
	//System connection pool
	Connection conn = getConnection();
	//gets the information from the customer table
	ResultSet resultSet = getResultSet(conn,"select * from Users");
	ResultSetMetaData metaData = getResultSetMetaData(resultSet);
	int numberOfColums;
	try 
	{
		numberOfColums = metaData.getColumnCount();
		String data[] = new String[numberOfColums];
		
	
		for (int i = 1; i<=numberOfColums;i++){
	
		}
	
		//add the customer sql data into the program
		while(resultSet.next()){
			for(int i = 1;i<=numberOfColums;i++)
			{
				
				data[i-1] = ""+ resultSet.getObject(i);
			}
			boolean trueFalse = false;
			if(data[3].equals("true"))
			{
				trueFalse = true;
			}
		
			modelClass.addUser(data[0], data[1], data[2], trueFalse);
		}
	} catch (SQLException e) 
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
	}

}


public void syncAccountsData()
{
	
	int numberOfColumsTransactions;
	try
	{
		
		selectAccounts = (PreparedStatement) conn.prepareStatement( 
	            "SELECT * FROM Accounts ");

	ResultSet resultSetAccounts = selectAccounts.executeQuery();
	ResultSetMetaData metaDataAccounts = getResultSetMetaData(resultSetAccounts);
	
	numberOfColumsTransactions = metaDataAccounts.getColumnCount();
	while(resultSetAccounts.next())
		{
		String dataAccounts[] = new String[numberOfColumsTransactions];
		for(int i = 1 ; i <= numberOfColumsTransactions ; i++)
		{
			dataAccounts[i-1] = ""+ resultSetAccounts.getObject(i);
		}
		modelClass.addAccount(Integer.parseInt(dataAccounts[0]), dataAccounts[1], Double.parseDouble(dataAccounts[2]), dataAccounts[3]);
		}
	}catch (SQLException e) 
	{
				// TODO Auto-generated catch block
				e.printStackTrace();
				
			}
}
//******************************************************************************************
public void syncTransactionData (int accountNumber)
{
	
	int numberOfColumsTransactions;
	try
	{
		// Returns only the current users transaction. Done for performance and security purposes
		selectTransaction = (PreparedStatement) conn.prepareStatement( 
	            "SELECT * FROM Transactions " + 
	            "WHERE fromAccount = ? OR toAccount = ?");
		
		

		selectTransaction.setInt(1, accountNumber);
		selectTransaction.setInt(2, accountNumber);
	ResultSet resultSetTransactions = selectTransaction.executeQuery();
	ResultSetMetaData metaDataTransactions = getResultSetMetaData(resultSetTransactions);
	
	numberOfColumsTransactions = metaDataTransactions.getColumnCount();
	while(resultSetTransactions.next())
		{
		String dataTransactions[] = new String[numberOfColumsTransactions];
		for(int i = 1 ; i <= numberOfColumsTransactions ; i++)
		{
			dataTransactions[i-1] = ""+ resultSetTransactions.getObject(i);
		}
		modelClass.addTransaction(Integer.parseInt(dataTransactions[0]), Integer.parseInt(dataTransactions[1]), Integer.parseInt(dataTransactions[2]), Double.parseDouble(dataTransactions[3]), dataTransactions[4], dataTransactions[5]);
		}

	}catch (SQLException e) 
	{
				// TODO Auto-generated catch block
				e.printStackTrace();
				
			}
}

public void syncCategoriesData (String ID)
{
	
	int numberOfColumsCategories;
	try
	{
		
		selectCategories = (PreparedStatement) conn.prepareStatement( 
	            "SELECT * FROM Categories " + 
	            "WHERE ID = ?");

		selectCategories.setString(1, ID);
	ResultSet resultSetCategories = selectCategories.executeQuery();
	ResultSetMetaData metaDataCategories = getResultSetMetaData(resultSetCategories);
	
	numberOfColumsCategories = metaDataCategories.getColumnCount();
	while(resultSetCategories.next())
		{
		String dataCategories[] = new String[numberOfColumsCategories];
		for(int i = 1 ; i <= numberOfColumsCategories ; i++)
		{
			
			dataCategories[i-1] = ""+ resultSetCategories.getObject(i);
		}
		modelClass.addCategory(Integer.parseInt(dataCategories[0]), dataCategories[1], Double.parseDouble(dataCategories[2]), dataCategories[3], dataCategories[4]);
		}
	
	
	}catch (SQLException e) 
	{
				// TODO Auto-generated catch block
				e.printStackTrace();
				
			}
}

public void addUserToDB(String ID, String fullName, String password, boolean isAdmin)
{
	try
	{
		insertUser = (PreparedStatement) conn.prepareStatement( 
	            "INSERT INTO Users " + 
	            "(ID , fullName, password, isAdmin) " + 
	            "VALUES ( ?, ?, ?, ?)" );
		insertUser.setString(1, ID);
		insertUser.setString(2, fullName);
		insertUser.setString(3, password);
		insertUser.setBoolean(4, isAdmin);
		insertUser.executeUpdate();
	} catch (SQLException e) 
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}

public void addAccountToDB(String ID, double balance, String accountName)
{
	try
	{
		insertAccount = (PreparedStatement) conn.prepareStatement( 
	            "INSERT INTO Accounts " + 
	            "(ID, balance, accountName ) " + 
	            "VALUES ( ?, ?, ?)" );
		insertAccount.setString(1, ID);
		insertAccount.setDouble(2, balance);
		insertAccount.setString(3, accountName);
		insertAccount.executeUpdate();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}

public void deleteAllTransactionsFromDB(int accountNumber)
{
try
{
	
	deleteTransaction = (PreparedStatement) conn.prepareStatement( 
            "DELETE FROM Transactions " + 
            "WHERE toAccount = ? OR fromAccount = ?" );
	deleteTransaction.executeUpdate();
}catch(SQLException e)
{
	e.printStackTrace();
}
}


public void deleteAccountFromDB(int accountNumber)
{
	try
	{
		deleteAccount = (PreparedStatement) conn.prepareStatement( 
	            "DELETE FROM Accounts " + 
	            "WHERE accountNumber = ?" );
		deleteAccount.setInt(1, accountNumber);
		deleteAccount.executeUpdate();
		
		deleteTransaction = (PreparedStatement) conn.prepareStatement( 
	            "DELETE FROM Transactions " + 
	            "WHERE toAccount = ? OR fromAccount = ?" );
		
		deleteTransaction.setInt(1, accountNumber);
		deleteTransaction.setInt(2, accountNumber);
		deleteTransaction.executeUpdate();
		
	} catch (SQLException e)
	{
		e.printStackTrace(); //Prints the SQL stacktrace
	}
}
public void editUserFromDB(String ID, String fullName, String password, boolean isAdmin)
{
	try
	{
		editUser = (PreparedStatement) conn.prepareStatement( 
	            "UPDATE Users " + 
	            "SET fullName = ?, password  = ?, isAdmin = ? " + 
	            "WHERE ID = ?" );
		editUser.setString(1, fullName);
		editUser.setString(2, password);
		editUser.setBoolean(3, isAdmin);
		editUser.setString(4, ID);
		editUser.executeUpdate();
		
	} catch (SQLException e) 
	{
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public void editBalance(double balance, int accountNumber)
{
	try
	{
		editAccount = (PreparedStatement) conn.prepareStatement( 
				("UPDATE accounts " + 
			            "SET balance = ? WHERE accountNumber = ? "));
		editAccount.setDouble(1, balance);
		editAccount.setInt(2, accountNumber);
		editAccount.executeUpdate();
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
public void deleteUserFromDB(String ID)
{
	try{
		deleteUser = (PreparedStatement) conn.prepareStatement( 
	            "DELETE FROM Users " + 
	            "WHERE ID = ?");
		deleteUser.setString(1, ID);
		deleteUser.executeUpdate();
		
		deleteAccount = (PreparedStatement) conn.prepareStatement( 
	            "DELETE FROM Accounts " + 
	            "WHERE ID = ?" );
		deleteAccount.setString(1, ID);
		deleteAccount.executeUpdate();
		
		deleteCategories = (PreparedStatement) conn.prepareStatement( 
	            "DELETE FROM Categories " + 
	            "WHERE ID = ?" );
		deleteCategories.setString(1, ID);
		deleteCategories.executeUpdate();
		
		

		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

public void deleteTransactionsFromDB(int accountNumber)
{
	try{
		deleteTransaction = (PreparedStatement) conn.prepareStatement( 
	            "DELETE FROM Transactions " + 
	            "WHERE toAccount = ? or fromAccount = ?" );
		
		deleteTransaction.setInt(1, accountNumber);
		deleteTransaction.setInt(2, accountNumber);
		deleteTransaction.executeUpdate();
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}


public void addTransactionToDB( double amount, int fromAccount, int toAccount, String transactionType, String transactionNote)
{
	try{
		
		insertTransaction = (PreparedStatement) conn.prepareStatement( 
	            "INSERT INTO Transactions " + 
	            "(amount, fromAccount, toAccount, transactionType, transactionNote) " + 
	            "VALUES ( ? ,? ,? ,? ,? )" );

		insertTransaction.setDouble(1, amount);
		insertTransaction.setInt(2, fromAccount);
		insertTransaction.setInt(3, toAccount);
		insertTransaction.setString(4, transactionType);
		insertTransaction.setString(5, transactionNote);
		insertTransaction.executeUpdate();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}
public void insertCategoryFromDB (String ID, String categoryName, double categoryAmount, String categoryType)
{
	try
	{
	insertCategory = (PreparedStatement) conn.prepareStatement( 
            "INSERT INTO Categories " + 
            "(ID, categoryName, categoryAmount, categoryType) " + 
            "VALUES ( ? ,? ,? ,?)" );
	
	insertCategory.setString(1, ID);
	insertCategory.setString(2, categoryName);
	insertCategory.setDouble(3, categoryAmount);
	insertCategory.setString(4, categoryType);
	insertCategory.executeUpdate();
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
}

public void deleteCategoryFromDB (int categoryNumber)
{
	try
	{
	insertCategory = (PreparedStatement) conn.prepareStatement( 
            "DELETE FROM Categories " + 
            "WHERE categoryNumber = ?");
	
	insertCategory.setInt(1, categoryNumber);
	insertCategory.executeUpdate();
} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
}
}
public static void setDatabaseInfo(String _sqlUrl, String _sqlPort, String db)
{
	sqlUrl ="jdbc:mysql://" + _sqlUrl + ":" + _sqlPort + "/";

	if(db != null && db.length() > 0)
		sqlUrl += db;
}

private DBconnection()
{
    try
    {
        Class.forName(sqlDriver).newInstance();
    } 
    
    catch(Exception ex) 
    {
        System.out.println(ex);
    }
}
public static DBconnection getInstance()
{
	if (instance == null)
		instance = new DBconnection();
	
	return instance;
}
public ResultSetMetaData getResultSetMetaData(ResultSet resultSet){
	ResultSetMetaData metaData = null;
	try {
		metaData = resultSet.getMetaData();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return metaData;
}

public ResultSet getResultSet(Connection connection, String qeury){
	
	try {
		statement = connection.createStatement();
		resultSet = statement.executeQuery(qeury);
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return resultSet;
	}

public Connection getConnection()
{
	Connection conn = null;
	try 
 	{
 		conn = DriverManager.getConnection(sqlUrl,sqlUser,sqlPasswd);
		} catch (SQLException e) {
			
		}
	return conn;
}


/**
 * Testing class which is used by developers to check if the connection
 * to SQL database is established.
 * @return the result of test. if a SQL Exception is thrown, it's catched and the SQL stack trace is shown in console.
 */
 public String testConnection()
 {
 	
 	String result = "Succesfully connected";
 	
 	try 
 	{
 		conn = DriverManager.getConnection(sqlUrl,sqlUser,sqlPasswd);
			if(conn.isValid(5000))
				return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
 }
}
